Create              proc sp_load_final_inventory_by_partnumber_and_lineitem_gp
(@mfgPartNumber varchar(50), @lineItem_id int, @cslit_id int)
As
declare @process_step_id bigint
declare @process_type varchar(20)
declare @process_type_name varchar(20)
declare @results Table(	Lot_Number varchar(20),
			Lot_ID bigint, 
			Location_ID int,
			Location varchar(20),
			Lot_Date_Code varchar(50),
			Step_Name varchar(20),
			Date_In datetime,
			Qty_In int,
			Balance_Qty int,
			Reserved_Qty int,
			Quantity_Pull int)

--declare @temp_cslit_id int
declare @temp_lot_id int
declare @temp_process_step_id bigint
declare @temp_location_id int
declare @temp_lot_number varchar(20)
declare @temp_location varchar(20)
declare @temp_lot_date_code varchar(50)
declare @temp_step_name varchar(20)
declare @temp_date_in datetime
declare @temp_qty_in int
declare @temp_bal_qty int
declare @temp_ship_qty int
declare @temp_qty_pull int
declare @tempCursor CURSOR
declare @pfcursor_deallocate_status int
declare @partNumber varchar(50)
declare @reserved_quantity int
declare @max_time_stamp datetime

set @pfcursor_deallocate_status = 0
select @temp_process_step_id = ProcessStep_ID from Product where Part_Number =  @mfgPartNumber and p_marketing_Part_Number is not null

declare pfCursor CURSOR for
select *from fn_get_process_flow_by_id(@temp_process_step_id)
open pfCursor
	fetch next from pfCursor
	into @process_step_id, @process_type, @process_type_name
	while @@fetch_status = 0
	begin
		print '@Process_type_name = '+ @process_type_name
		if @process_type_name = 'Finished Goods'
		begin
			set @tempCursor = CURSOR for
			select lt.Lot_ID, lt.Lot_Number, lt.Location_ID, lt.Lot_Date_Code from Lot_Table lt where ProcessStep_ID = @process_step_id and ( Lot_Current_Status = 'Running' /*or   lot_id in (select lot_id from customer_shipping_lot_number_table where cslit_id = @cslit_id ) */) order by location_id
			
			open @tempCursor
			fetch next from @tempCursor
			into @temp_lot_id, @temp_lot_number, @temp_location_id, @temp_lot_date_Code
			while @@fetch_status = 0
			begin
	
				select @max_time_stamp = max(timestamp) from Inventory_Lot where Lot_ID = @temp_lot_id
				if @max_time_stamp is null
				begin
					select @temp_qty_in = IL_Qty_In, @temp_date_in = IL_Date_In, @temp_bal_qty = IL_Qty_Balance from Inventory_Lot where Lot_ID = @temp_lot_id
				end

				else
				begin
					select @temp_qty_in = IL_Qty_In, @temp_date_in = IL_Date_In, @temp_bal_qty = IL_Qty_Balance from Inventory_Lot where Lot_ID = @temp_lot_id and timestamp = @max_time_stamp
				end

				if @temp_bal_qty is null
				begin
					set @temp_bal_qty = @temp_qty_in
				end

				set @temp_qty_pull = -1
				-- select @temp_qty_pull = CSLNT_Qty_Pull from customer_shipping_lot_number_table cslnt where cslit_id = @cslit_id and Lot_ID = @temp_lot_id

				/*select @temp_ship_qty = sum(CSLNT_Qty_Pull) from customer_shipping_lot_number_table where Lot_ID = @temp_lot_id
				if @temp_ship_qty is null
				begin
					set @temp_ship_qty = 0
				end*/

				select @temp_location = (select v.V_Name + '/'+ a.City from Location l, Vendor v, Address a where l.Location_ID = @temp_location_id and l.Vendor_ID = v.Vendor_ID and l.Address_ID = a.Address_ID)

				set @reserved_quantity = 0
				select @reserved_quantity = lrt_lot_quantity from lot_reservation_table lrt, customer_open_order_lineitem cool where cool.lineItem_id = @lineItem_id and cool.cool_line_item_no = lrt.lrt_line_item_no and cool.salesOrder_Id = lrt.lrt_salesOrder_id and lrt.lrt_lot_number = @temp_lot_number

				if @temp_qty_in = -1
				begin
					update @results set lot_id = -1 where lot_id = -1
				end
	
				else
				begin
					insert into @results( Lot_Number, Lot_ID, Location_ID, Location, Lot_Date_Code, Step_Name, Date_In, Qty_In, Balance_Qty, Reserved_Qty, Quantity_Pull) values( @temp_lot_number, @temp_lot_id, @temp_location_id, @temp_location, @temp_lot_date_code, @process_type_name, @temp_date_in, @temp_qty_in, @temp_bal_qty, @reserved_quantity, @temp_qty_pull)
				end
				fetch next from @tempCursor
				into @temp_lot_id, @temp_lot_number, @temp_location_id, @temp_lot_date_Code

			end
			close @tempCursor 
			deallocate @tempCursor
			set @pfcursor_deallocate_status = 1
		end

		else if @process_type_name = 'TEST'
		begin
			set @tempCursor = CURSOR for
			select Lot_ID, Lot_Number, Location_ID, Lot_Date_Code  from Lot_Table where ProcessStep_ID = @process_step_id and ( ( Lot_Current_Status = 'Running'  or Lot_Current_Status = 'TRANSIT')/* or lot_id in (select lot_id from customer_shipping_lot_number_table where cslit_id = @cslit_id )*/ ) order by location_id
			
			open @tempCursor
			fetch next from @tempCursor
			into @temp_lot_id, @temp_lot_number, @temp_location_id, @temp_lot_date_Code

			while @@fetch_status = 0
			begin
				select @max_time_stamp = max(timestamp) from Inventory_Lot where Lot_ID = @temp_lot_id
				if @max_time_stamp is null
				begin
					select @temp_qty_in = isnull(IL_Qty_In, -1) , @temp_date_in = IL_Date_In, @temp_bal_qty = IL_Qty_Balance from inventory_Lot where Lot_ID = @temp_lot_id
				end

				else
				begin
					select @temp_qty_in = isnull(IL_Qty_In, -1) , @temp_date_in = IL_Date_In, @temp_bal_qty = IL_Qty_Balance from inventory_Lot where Lot_ID = @temp_lot_id and timestamp = @max_time_stamp
				end

				if @temp_bal_qty is null
				begin
					set @temp_bal_qty = @temp_qty_in
				end

				set @temp_qty_pull = -1
				-- select @temp_qty_pull = CSLNT_Qty_Pull from customer_shipping_lot_number_table cslnt where cslit_id = @cslit_id and Lot_ID = @temp_lot_id

				/*select @temp_ship_qty = sum(CSLNT_Qty_Pull) from customer_shipping_lot_number_table where Lot_ID = @temp_lot_id
				if @temp_ship_qty is null
				begin
					set @temp_ship_qty = 0
				end*/

				select @temp_location = (select v.V_Name + '/'+ a.City from Location l, Vendor v, Address a where l.Location_ID = @temp_location_id and l.Vendor_ID = v.Vendor_ID and l.Address_ID = a.Address_ID)

				set @reserved_quantity = 0
				select @reserved_quantity = lrt_lot_quantity from lot_reservation_table lrt, customer_open_order_lineitem cool where cool.lineItem_id = @lineItem_id and cool.cool_line_item_no = lrt.lrt_line_item_no and cool.salesOrder_Id = lrt.lrt_salesOrder_id and lrt.lrt_lot_number = @temp_lot_number

				if @temp_qty_in = -1
				begin
					update @results set lot_id = -1 where lot_id = -1
				end
	
				else
				begin
					insert into @results( Lot_Number, Lot_ID, Location_ID, Location, Lot_Date_Code, Step_Name, Date_In, Qty_In, Balance_Qty, Reserved_Qty, Quantity_Pull) values( @temp_lot_number, @temp_lot_id, @temp_location_id, @temp_location, @temp_lot_date_code, @process_type_name, @temp_date_in, @temp_qty_in, @temp_bal_qty, @reserved_quantity, @temp_qty_pull)
				end

				fetch next from @tempCursor
				into @temp_lot_id, @temp_lot_number, @temp_location_id, @temp_lot_date_Code

			end
			close @tempCursor
			deallocate @tempCursor

			set @pfcursor_deallocate_status = 1
		end

		set @temp_qty_in = -1
		set @max_time_stamp = null

		fetch next from pfCursor
		into @process_step_id, @process_type, @process_type_name
	end

	if @pfcursor_deallocate_status = 1
	begin
		--deallocate @tempCursor
			
		close pfCursor
		deallocate pfCursor
		select *from @results
		return
	end

		
close pfCursor
deallocate pfCursor
